from odoo import fields, models, _
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT


class ReportStockInInternalOut(models.Model):
    _name = 's.report.stock.in.internal.out'
    _description = 'Report Stock In Internal Out'
    _order = 'done_date'
    _auto = False

    partner_id = fields.Many2one('res.partner', string='Partner')
    picking_id = fields.Many2one('stock.picking', string='Picking')
    product_id = fields.Many2one('product.product', string='Product')
    product_category_id = fields.Many2one('product.category', string='Product Category')
    location_id = fields.Many2one('stock.location', string='Source Location')
    location_dest_id = fields.Many2one('stock.location', 'Destination Location')
    product_qty = fields.Float(string='Quantity', digits='Product Unit of Measure', default=0)
    uom_id = fields.Many2one('uom.uom', string='Uom')
    lot_id = fields.Many2one('stock.lot', string='Lot')
    done_date = fields.Datetime(string='Done Date')
    price = fields.Float('Price')
    price_total = fields.Monetary(string='Total', currency_field='currency_id')
    currency_id = fields.Many2one('res.currency', string='Currency')

    area = fields.Float(string='Area')

    def action_report_at_date(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        product_id = self._context.get('product_id')
        business_type = self._context.get('business_type')
        business_data = ','.join([business_type or '', start_date or '', end_date or '', product_id or ''])
        return {
            'type': 'ir.actions.act_url',
            'url': f'/download/report/stock/in/internal/out/excel/{business_data or ""}',
            'target': 'new',
        }

    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        product_id = self._context.get('product_id')
        business_type = self._context.get('business_type')
        where = ''
        where_list = ["sml.state = 'done'", "sm.s_report_not_counted = False"]
        if product_id:
            where_list.append(f"pp.id = {product_id}")

        if start_date:
            where_list.append(f"sm.date >= '{start_date}'")
        if end_date:
            where_list.append(f"sm.date <= '{end_date}'")
        if business_type:
            production_location_ids = self.env['stock.location'].search([('barcode', '=', 'WH-PREPRODUCTION')], limit=1)
            production_location_ids |= production_location_ids.mapped('child_ids')

            if business_type == 'sale':
                where_list.append(f"((sl.usage = 'customer' and sl2.usage = 'internal') or (sl2.usage = 'customer' and sl.usage = 'internal'))")
            elif business_type == 'purchase':
                where_list.append(f"((sl.usage = 'supplier' and sl2.usage = 'internal') or (sl2.usage = 'supplier' and sl.usage = 'internal'))")
            elif business_type == 'finished_in':
                where_list.append(f"sl2.barcode = 'WH-STOCK-FINISHED-PRODUCT' and cte2.lot_id is null")
            elif business_type == 'material_scrap':
                where_list.append(f"pc.code not in ('BCP', 'CHP')")
                where_list.append(f"(sl.scrap_location = true or sl2.scrap_location = true)")
                where_list.append(f"sl.id not in {tuple(production_location_ids.ids)} and sl2.id not in {tuple(production_location_ids.ids)}")
        if where_list:
            where = f"where {' and '.join(where_list)}"

        # 成品入库
        if business_type and business_type == 'finished_in':
            table_sql = f"""
            with cte as (select distinct lot.id lot_id, sol.price_unit, sol.currency_id
             from mrp_production mp
                      left join stock_move on mp.id = stock_move.production_id
                      left join stock_move_line on stock_move.id = stock_move_line.move_id
                      left join stock_lot lot on stock_move_line.lot_id = lot.id
                      left join mrp_production_sale_order_line_rel mpsolr on mp.id = mpsolr.mrp_production_id
                      left join sale_order_line sol on mpsolr.sale_order_line_id = sol.id
             where sol.currency_id is not null
               and mp.state = 'done'),
                cte2 as (
                    select distinct stock_move_line.lot_id lot_id
                         from mrp_production
                                  left join stock_move on mrp_production.id = stock_move.production_id
                                  left join stock_move_line on stock_move.id = stock_move_line.move_id
                         where stock_move_line.state = 'done'
                         and mrp_production.mo_type = 'W'
                )
            select row_number() OVER () AS       id,
                   pp.id                         product_id,
                   pc.id                         product_category_id,
                   sl.id                         location_id,
                   sl2.id                        location_dest_id,
                   rp.id                         partner_id,
                   sp.id                         picking_id,
                   lot.id                        lot_id,
                   sml.product_uom_id            uom_id,
                   sml.quantity                  product_qty,
                   sml.quantity / COALESCE(puvc.uom_value) * puva.uom_value * puvb.uom_value / 1000000 area,
                   cte.price_unit                price,
                   cte.currency_id               currency_id,
                   cte.price_unit * sml.quantity price_total,
                   sml.date                      done_date
            from stock_move_line sml
                     left join stock_move sm on sml.move_id = sm.id
                     left join stock_picking sp on sm.picking_id = sp.id
                     left join res_partner rp on rp.id = sp.partner_id
                     left join product_product pp on sml.product_id = pp.id
                     left join product_template pt on pp.product_tmpl_id = pt.id
                     left join product_uom_value puva on puva.product_id = pt.id and puva.uom_id in (select id from uom_uom where uom_uom.code = 'SETL')
                     left join product_uom_value puvb on puvb.product_id = pt.id and puvb.uom_id in (select id from uom_uom where uom_uom.code = 'SETW')
                     left join product_uom_value puvc on puvc.product_id = pt.id and puvc.uom_id in (select id from uom_uom where uom_uom.code = 'SET')
                     left join stock_location sl on sml.location_id = sl.id
                     left join stock_location sl2 on sml.location_dest_id = sl2.id
                     left join stock_lot lot on sml.lot_id = lot.id
                     left join product_category pc on pt.categ_id = pc.id
                     left join cte on cte.lot_id = lot.id
             left join cte2 on cte2.lot_id = lot.id 
            {where}
            
            order by sml.date        
            """

        else:
            table_sql = f"""
                select 
                       row_number() OVER() AS id,
                       sml.product_id       product_id,
                       sml.location_id      location_id,
                       sml.location_dest_id location_dest_id,
                       sm.date              done_date,
                       sml.quantity         product_qty,
                       sml.product_uom_id   uom_id,
                       sp.partner_id        partner_id,
                       pc.id product_category_id,
                       lot.id lot_id,
                       sp.id picking_id,
                       0 price,
                       0 price_total,
                       null currency_id,
                       0 area
                from stock_move_line sml
                         left join stock_move sm on sml.move_id = sm.id
                         left join stock_picking sp on sm.picking_id = sp.id
                         left join product_product pp on pp.id = sml.product_id
                         left join product_template pt on pp.product_tmpl_id = pt.id
                         left join product_category pc on pt.categ_id = pc.id
                         left join stock_lot lot on sml.lot_id = lot.id
                         left join stock_location sl on sml.location_id = sl.id
                         left join stock_location sl2 on sml.location_dest_id = sl2.id
                {where}
        """
        return table_sql
